package cn.dida.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import cn.dida.model.Pageable;
import cn.dida.model.UserSession;
import cn.dida.util.NumberConvetUtil;

/**
 * @author weiwei
 */
@Repository
public class QrcodeDao extends DaoBase {
	
	public int maxexecutenub = 50000;
	

	public Pageable<Map<String, Object>> queryAllQrcodeByCompnayID(Integer companyId, Map<String, Object> paramMap,	Integer page) {
		  	
			
			if(null==paramMap){
				paramMap = new HashMap<String, Object>();
			}
		
			Object[] args = {};
	        StringBuffer sqlQueryResultCount = new StringBuffer(" SELECT COUNT(1) FROM qrcode_base ");
	        StringBuffer sqlQueryResult = new StringBuffer("SELECT id,qcname,start_no minid,end_no maxid,hava_no total ,"
	        		+ "bandings_nub  bd ,"
	        		+ "scan_no scan_nub,"
	        		+ "type,"
	        		+ "c_time,"
	        		+ "c_admin,"
	        		+ "is_print, is_history "
	        		+ "FROM qrcode_base ");

	        StringBuffer sqlWhere = new StringBuffer(" where company_id = ? and source = 0  ");
	       
	        args = ArrayUtils.add(args, companyId);
	        if(paramMap.containsKey("qcname")){
	        	sqlWhere.append(" and qcname like ?  ");
	        	args = ArrayUtils.add(args, paramMap.get("qcname") + "%");
	        }
	        
	        if(paramMap.containsKey("state")){
	        	sqlWhere.append(" and type = ?  ");
	        	args = ArrayUtils.add(args, NumberUtils.toByte(paramMap.get("state").toString(),(byte) -1));
	        }
	        
	     
	        if(paramMap.containsKey("tsno")){
	        	sqlWhere.append(" and hava_no >= ?  ");
	        	args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("tsno").toString(),0));
	        }
	        
	        if(paramMap.containsKey("teno")){
	        	sqlWhere.append(" and hava_no <= ?  ");
	        	args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("teno").toString(),0));
	        }
	        
	        if(paramMap.containsKey("ysno")){
	        	sqlWhere.append(" and scan_no >= ?  ");
	        	args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("ysno").toString(),0));
	        }
	        
	        if(paramMap.containsKey("yeno")){
	        	sqlWhere.append(" and scan_no <= ?  ");
	        	args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("yeno").toString(),0));
	        }
	        
	        if(paramMap.containsKey("is_print")){
	        	sqlWhere.append(" and is_print = ?  ");
	        	args = ArrayUtils.add(args, NumberUtils.toByte(paramMap.get("is_print").toString(),(byte) -1));
	        }
	        
	        // 查询记录总数
	        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
	        // 查询记录
	        sqlQueryResult.append(sqlWhere).append("  ORDER BY c_time desc   limit ?,?");
	        args = ArrayUtils.add(args, (page - 1) * getPageSize());
	        args = ArrayUtils.add(args, getPageSize());
	        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
	        return new Pageable<>(page, getPageSize(), resultTotal, result);
	    }
	
	public List<Map<String,Object>> queryAllQrcodeByCompnayIDForExport(Integer companyId, Map<String, Object> paramMap) {
		
		if(null==paramMap){
			paramMap = new HashMap<String, Object>();
		}
		
		Object[] args = {};
		
		StringBuffer sqlQueryResult = new StringBuffer("SELECT id,qcname,start_no minid,end_no maxid,hava_no total ,"
				+ "bandings_nub  bd ,"
				+ "scan_no scan_nub,"
				+ "type,"
				+ "c_time,"
				+ "c_admin,"
				+ "is_print "
				+ "FROM qrcode_base  ");
		
		StringBuffer sqlWhere = new StringBuffer(" where company_id = ? and source = 0  ");
		
		args = ArrayUtils.add(args, companyId);
		if(paramMap.containsKey("qcname")){
			sqlWhere.append(" and qcname like ?  ");
			args = ArrayUtils.add(args, paramMap.get("qcname") + "%");
		}
		
		if(paramMap.containsKey("state")){
			sqlWhere.append(" and type = ?  ");
			args = ArrayUtils.add(args, NumberUtils.toByte(paramMap.get("state").toString(),(byte) -1));
		}
		
		
		if(paramMap.containsKey("tsno")){
			sqlWhere.append(" and hava_no >= ?  ");
			args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("tsno").toString(),0));
		}
		
		if(paramMap.containsKey("teno")){
			sqlWhere.append(" and hava_no <= ?  ");
			args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("teno").toString(),0));
		}
		
		if(paramMap.containsKey("ysno")){
			sqlWhere.append(" and scan_no >= ?  ");
			args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("ysno").toString(),0));
		}
		
		if(paramMap.containsKey("yeno")){
			sqlWhere.append(" and scan_no <= ?  ");
			args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("yeno").toString(),0));
		}
		
		if(paramMap.containsKey("is_print")){
			sqlWhere.append(" and is_print = ?  ");
			args = ArrayUtils.add(args, NumberUtils.toByte(paramMap.get("is_print").toString(),(byte) -1));
		}
		
		// 查询记录
		sqlQueryResult.append(sqlWhere).append("  ORDER BY c_time desc ");
		
		List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
		return result;
	}

	
	


	
	public int insertQrcodeBase(String qcname, Byte type, Integer nubs, UserSession userSession,Long nowlong,String admin) throws Exception {
		// TODO Auto-generated method stub
		String sql = "insert into qrcode_base (company_id,qcname,type,hava_no,c_time,c_admin,is_history,source) values (?,?,?,?,?,?,?,?)";
	    KeyHolder keyHolder = new GeneratedKeyHolder();  
	    jdbcTemplate.update(new PreparedStatementCreator() { 
	    	@Override
	        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {  
	               PreparedStatement ps = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);  
	               ps.setInt(1, userSession.getCompanyId()); 
	               ps.setString(2,  qcname);
	               ps.setByte(3,  type); 
	               ps.setInt(4, nubs); 
	               ps.setLong(5, nowlong); 
	               ps.setString(6, admin); 
	               //20170308 改造后新增二维码is_history字段为1 区分下载二维码
	               ps.setInt(7, 1);
	               ps.setInt(8, 0);
	               return ps;  
	        }  
	    }, keyHolder);  
	      
	    int generatedId = keyHolder.getKey().intValue();   
	    return generatedId;  
	    
	}
	
	public static void main(String[] args) {
		ExecutorService executor = Executors.newFixedThreadPool(3);
		int a=101;
		int max = 10;
		while(a>max){
			executor.submit(() -> {
				try {
					TimeUnit.SECONDS.sleep(10);
				} catch (InterruptedException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			String threadName = Thread.currentThread().getName();
			System.out.println("Hello " + threadName);
			});
			
			 a = a - max;
		}
	
		if(a>0){
			executor.submit(() -> {
				try {
					TimeUnit.SECONDS.sleep(10);
				} catch (InterruptedException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			String threadName = Thread.currentThread().getName();
			System.out.println("Hellofinal " + threadName);
			});
		}
	    System.out.println("attempt to shutdown executor");
	    executor.shutdown();
		
//		try {
//		    System.out.println("attempt to shutdown executor");
//		    executor.shutdown();
//		    //executor.awaitTermination(20, TimeUnit.SECONDS);
//		    }
//		catch (InterruptedException e) {
//		    System.err.println("tasks interrupted");
//		}
//		finally {
//		    if (!executor.isTerminated()) {
//		        System.err.println("cancel non-finished tasks");
//		    }
//		    executor.shutdownNow();
//		    System.out.println("shutdown finished");
//		}
	}

//	public int insertQrcodeDetail(int qrcode_base_id, Integer nubs) {
//		// TODO Auto-generated method stub
//		
//		ExecutorService executor = Executors.newFixedThreadPool(10);
//		
//		int tmpnubs = nubs;
//		
//		Long nowlong = Calendar.getInstance().getTimeInMillis();
//		
//		
//		System.out.println("start:"+nowlong);
//
//	
//		 
//		 while(tmpnubs>maxexecutenub){
//			 
//			 
//			 	executor.submit(() -> {
//					String prefix = "insert into qrcode_detail (uuid,qrcode_base_id) values ";
//					StringBuffer suffix = new StringBuffer(); 
//					for(int i=0;i<maxexecutenub;i++){
//						 suffix.append("( '" +UUID.randomUUID().toString().replace("-", "") + "',"+qrcode_base_id+ " ),");  
//					}
//				    String sql = prefix + suffix.substring(0, suffix.length() - 1);  
//				    
//				    jdbcTemplate.execute(sql);
//				    System.out.println("超过限制数量，执行限制数量添加--------------"+Thread.currentThread().getName());
//			 
//			 	});
//			 
//		
//			    tmpnubs = tmpnubs - maxexecutenub;
//			    
//		 }
//		 
//		 if(tmpnubs>0){
//			 final int leftnubs = tmpnubs;
//			 	executor.submit(() -> {
//				    String prefix = "insert into qrcode_detail (uuid,qrcode_base_id) values ";
//					StringBuffer suffix = new StringBuffer(); 
//					for(int i=0;i<leftnubs;i++){
//						 suffix.append("( '" +UUID.randomUUID().toString().replace("-", "") + "',"+qrcode_base_id+ " ),");  
//					}
//				    String sql = prefix + suffix.substring(0, suffix.length() - 1);  
//					    
//				    jdbcTemplate.execute(sql);
//				    
//				    System.out.println("未超过限制数量，执行限制数量添加----最后----------"+Thread.currentThread().getName());
//			 
//			 	});
//			 
//
//		    
//		 }
//	    System.out.println("attempt to shutdown executor");
//	    executor.shutdown();
//	    
//	    Long afterlong = Calendar.getInstance().getTimeInMillis();
//		System.out.println("end:"+afterlong);
//		System.out.println("cost:"+(afterlong-nowlong));
//		return 1;
//	}
//	
	public int insertQrcodeDetail(int qrcode_base_id, Integer nubs, Integer companyid) {
		// TODO Auto-generated method stub
		int tmpnubs = nubs;
		Long nowlong = Calendar.getInstance().getTimeInMillis();
		//System.out.println("start:"+nowlong);
		 while(tmpnubs>maxexecutenub){
				String prefix = "insert into qrcode_detail_"+companyid+" (uuid,qrcode_base_id,source) values ";
				StringBuffer suffix = new StringBuffer(); 
				for(int i=0;i<maxexecutenub;i++){
					 suffix.append("( '" +UUID.randomUUID().toString().replace("-", "") + "',"+qrcode_base_id+ " ,0),");  
				}
			    String sql = prefix + suffix.substring(0, suffix.length() - 1);  
			    jdbcTemplate.execute(sql);
			   // System.out.println("超过限制数量，执行限制数量添加--------------"+tmpnubs);
			    tmpnubs = tmpnubs - maxexecutenub;
		 }
		 if(tmpnubs>0){
			    String prefix = "insert into qrcode_detail_"+companyid+" (uuid,qrcode_base_id,source) values ";
				StringBuffer suffix = new StringBuffer(); 
				for(int i=0;i<tmpnubs;i++){
					 suffix.append("( '" +UUID.randomUUID().toString().replace("-", "") + "',"+qrcode_base_id+ ",0 ),");  
				}
			    String sql = prefix + suffix.substring(0, suffix.length() - 1);  
			    jdbcTemplate.execute(sql);
			   // System.out.println("未超过限制数量，执行限制数量添加--------------"+tmpnubs);
		 }
	    Long afterlong = Calendar.getInstance().getTimeInMillis();
	  
		return 1;
	}


	public Map<String, Object> selectMinIDAndMaxIDByQrcodeBaseID(int qrcode_base_id, Integer companyid) {
		// TODO Auto-generated method stub
		
		String sql = "SELECT MIN(id) minid,MAX(id) maxid  FROM qrcode_detail_"+companyid+" WHERE qrcode_base_id = ?";
		
		Map<String, Object> rmap = jdbcTemplate.queryForMap(sql, qrcode_base_id);
		
		
		return rmap;
	}
	
	/**
	 * queryQrcodeBaseID 查询qrcode_base_id对应的所有二维码id
	 * @author kelan
	 * @date 2017年3月9日
	 * @param qrcode_base_id
	 * @param companyid
	 * @return
	 */
	public  List<Map<String,Object>> queryQrcodeBaseID(int qrcode_base_id, Integer companyid) {
		// TODO Auto-generated method stub
		String sql = "SELECT id FROM qrcode_detail_"+companyid+" WHERE qrcode_base_id = ?";
		List<Map<String,Object>> result = jdbcTemplate.queryForList(sql, qrcode_base_id);
		return result;
	}
	
	/**
	 * queryQrcodeBasePIDBId 查询qrcode_base_id对应的所有瓶箱码id
	 * @author kelan
	 * @date 2017年3月9日
	 * @param qrcode_base_id
	 * @param companyid
	 * @return
	 */
	public  List<Map<String,Object>> queryQrcodeBasePIDBId(int qrcode_base_id, Integer companyid) {
		// TODO Auto-generated method stub
		StringBuffer str = new StringBuffer();
		str.append(" select qr.id b_id,qr2.p_id from qrcode_detail_"+companyid+" qr  ");
		str.append(" join (select id as p_id,p_uuid from qrcode_detail_"+companyid+" where qrcode_base_id=? and p_uuid is not null )qr2 ");
		str.append(" on qr2.p_uuid=qr.uuid ");
		str.append(" where qr.qrcode_base_id=? ");
		List<Map<String,Object>> result = jdbcTemplate.queryForList(str.toString(), qrcode_base_id,qrcode_base_id);
		return result;
	}
	
	
	/**
	 * 新增二维码同时新增二维码与物流码关系
	 * @author kelan
	 * @date 2017年3月7日
	 * @param results
	 * @return
	 */
	public int insertLogresQrcode(List<Map<String,Object>> results, Integer companyid){
			// TODO Auto-generated method stub
			int snubs = 0;
		    int tmpnubs = results.size();
		    while(tmpnubs>maxexecutenub){
					String prefix = "insert into log_res_qrcode (p_id,pcode,uuid,companyid) values ";
					StringBuffer suffix = new StringBuffer(); 
					for(int i=0;i<maxexecutenub;i++){
						
						Map<String,Object> map = results.get(snubs);
						snubs+=1;
					    suffix.append("( '" +Integer.parseInt(map.get("id").toString()) + "',"
					    		+ "'"+NumberConvetUtil.baseString(NumberUtils.toLong(map.get("id").toString(), 0L),NumberConvetUtil.SIXTY_CONVET)+ "' ,"
					    				+ "'"+UUID.randomUUID().toString().replace("-", "")+"','"+companyid+"'),");  
					}
				    String sql = prefix + suffix.substring(0, suffix.length() - 1);  
				    jdbcTemplate.execute(sql);
				    
				    //System.out.println("超过限制数量，执行限制数量添加--------------"+tmpnubs);
				    tmpnubs = tmpnubs - maxexecutenub;
			 }
		    if(tmpnubs>0){
			    String prefix = "insert into log_res_qrcode (p_id,pcode,uuid,companyid) values  ";
				StringBuffer suffix = new StringBuffer(); 
				for(int i=0;i<tmpnubs;i++){
					Map<String,Object> map = results.get(snubs);
					snubs+=1;
					suffix.append("( '" +Integer.parseInt(map.get("id").toString()) + "',"
							+ "'"+NumberConvetUtil.baseString(NumberUtils.toLong(map.get("id").toString(), 0L),NumberConvetUtil.SIXTY_CONVET)+ "' ,"
									+ "'"+UUID.randomUUID().toString().replace("-", "")+"','"+companyid+"'),");  
				}
			    String sql = prefix + suffix.substring(0, suffix.length() - 1);  
			    jdbcTemplate.execute(sql);
			   // System.out.println("未超过限制数量，执行限制数量添加--------------"+tmpnubs);
		 }
	    return 1;  
	}
	
	/**
	 * 组合码新增时 向res_qrbind表中新增瓶箱关联关系
	 * @author kelan
	 * @date 2017年3月9日
	 * @param results
	 * @param userSession
	 * @return
	 */
	public int insertLogresQrcodeQrbing(List<Map<String,Object>> results,UserSession userSession){
		// TODO Auto-generated method stub
		int snubs = 0;
	    int tmpnubs = results.size();
	    while(tmpnubs>maxexecutenub){
				String prefix = "insert into res_qrbind (company_id,contact_way,p_qrcode_id,b_qrcode_id,qrcode_type,contact_type,c_admin,ts,bind_company_id) values";
				StringBuffer suffix = new StringBuffer(); 
				for(int i=0;i<maxexecutenub;i++){
					Map<String,Object> map = results.get(snubs);
					snubs+=1;
				    suffix.append("( '" +userSession.getCompanyId() + "',2,'"+Integer.parseInt(map.get("p_id").toString())+ "' ,'"+Integer.parseInt(map.get("b_id").toString())+"'"
				    		+ ",1,1,'"+userSession.getId()+"','"+Calendar.getInstance().getTimeInMillis()+"','" +userSession.getCompanyId() + "'),");  
				}
			    String sql = prefix + suffix.substring(0, suffix.length() - 1);  
			    jdbcTemplate.execute(sql);
			    
			    //System.out.println("超过限制数量，执行限制数量添加--------------"+tmpnubs);
			    tmpnubs = tmpnubs - maxexecutenub;
		 }
	    if(tmpnubs>0){
		    String prefix = "insert into res_qrbind (company_id,contact_way,p_qrcode_id,b_qrcode_id,qrcode_type,contact_type,c_admin,ts,bind_company_id) values  ";
			StringBuffer suffix = new StringBuffer(); 
			for(int i=0;i<tmpnubs;i++){
				Map<String,Object> map = results.get(snubs);
				snubs+=1;
				suffix.append("( '" +userSession.getCompanyId() + "',2,'"+Integer.parseInt(map.get("p_id").toString())+ "' ,'"+Integer.parseInt(map.get("b_id").toString())+"'"
			    		+ ",1,1,'"+userSession.getId()+"','"+Calendar.getInstance().getTimeInMillis()+"','" +userSession.getCompanyId() + "'),");  
			}
		    String sql = prefix + suffix.substring(0, suffix.length() - 1);  
		    jdbcTemplate.execute(sql);
		    //System.out.println("未超过限制数量，执行限制数量添加--------------"+tmpnubs);
	    }
		
	    return 1;  
	}
	
	public void updateQrcodeBH(int qrcode_base_id, Long start_no, Long end_no) {
		// TODO Auto-generated method stub
		
		String sql = "UPDATE qrcode_base SET start_no = ?,end_no = ? WHERE id = ? ";
		
		jdbcTemplate.update(sql, start_no,end_no,qrcode_base_id);
		
		
	}

	public Pageable<Map<String, Object>> queryAllQrcodeGroupByCompnayID(Integer companyId, Map<String, Object> paramMap, Integer page) {
		// TODO Auto-generated method stub
	  	Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer(" SELECT COUNT(1) FROM qrcode_base ");
        StringBuffer sqlQueryResult = new StringBuffer("SELECT id,pbc_id, qcname,start_no minid,end_no maxid,hava_no total ,"
        		+ "bandings_nub    bd ,"
        		+ "scan_no scan_nub,"
        		+ "g_start_no gminid,"
        		+ "g_end_no gmaxid,"
        		+ "g_hava_no gtotal,"
        		+ "g_bandings_nub   gbd,"
        		+ "g_scan_no gscan_nub,"
        		+ "c_time,"
        		+ "c_admin,"
        		+ "is_print "
        		+ "FROM qrcode_base ");

        StringBuffer sqlWhere = new StringBuffer(" where company_id = ? and source = 1 ");
       
        if(null==paramMap){
			paramMap = new HashMap<String, Object>();
		}
       
        args = ArrayUtils.add(args, companyId);
        if(paramMap.containsKey("qcname")){
        	sqlWhere.append(" and qcname like ?  ");
        	args = ArrayUtils.add(args, paramMap.get("qcname") + "%");
        }
        
        if(paramMap.containsKey("pbcId")){
        	sqlWhere.append(" and pbc_id = ?  ");
        	args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("pbcId").toString(),-1));
        }
        
     
        if(paramMap.containsKey("tsno")){
        	sqlWhere.append(" and (hava_no+g_hava_no) >= ?  ");
        	args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("tsno").toString(),0));
        }
        
        if(paramMap.containsKey("teno")){
        	sqlWhere.append(" and (hava_no+g_hava_no) <= ?  ");
        	args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("teno").toString(),0));
        }
        
        if(paramMap.containsKey("ysno")){
        	sqlWhere.append(" and (scan_no+g_scan_no) >= ?  ");
        	args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("ysno").toString(),0));
        }
        
        if(paramMap.containsKey("yeno")){
        	sqlWhere.append(" and (scan_no+g_scan_no)  <= ?  ");
        	args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("yeno").toString(),0));
        }
        
        if(paramMap.containsKey("xsno")){
        	sqlWhere.append(" and g_hava_no >= ?  ");
        	args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("xsno").toString(),0));
        }
        
        if(paramMap.containsKey("xeno")){
        	sqlWhere.append(" and g_hava_no <= ?  ");
        	args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("xeno").toString(),0));
        }
        
        if(paramMap.containsKey("psno")){
        	sqlWhere.append(" and hava_no >= ?  ");
        	args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("psno").toString(),0));
        }
        
        if(paramMap.containsKey("peno")){
        	sqlWhere.append(" and hava_no <= ?  ");
        	args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("peno").toString(),0));
        }
        
        if(paramMap.containsKey("is_print")){
        	sqlWhere.append(" and is_print = ?  ");
        	args = ArrayUtils.add(args, NumberUtils.toByte(paramMap.get("is_print").toString(),(byte) -1));
        }
        
        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  ORDER BY c_time desc   limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return new Pageable<>(page, getPageSize(), resultTotal, result);
        
	}
	/**
	 * 组合码数据导出列表
	 * @param companyId 公司id
	 * @param paramMap 查询条件集合
	 * @return List<Map<String, Object>>
	 * @date 2016-12-07 14:03
	 * */
	public List<Map<String, Object>> queryAllQrcodeGroupByCompnayIDForExport(Integer companyId, Map<String, Object> paramMap) {
		Object[] args = {};
		StringBuffer sqlQueryResult = new StringBuffer("SELECT id,pbc_id, qcname,start_no minid,end_no maxid,hava_no total ,"
				+ "bandings_nub    bd ,"
				+ "scan_no scan_nub,"
				+ "g_start_no gminid,"
				+ "g_end_no gmaxid,"
				+ "g_hava_no gtotal,"
				+ "g_bandings_nub   gbd,"
				+ "g_scan_no gscan_nub,"
				+ "c_time,"
				+ "c_admin,"
				+ "is_print "
				+ "FROM qrcode_base ");
		
		StringBuffer sqlWhere = new StringBuffer(" where company_id = ? and source = 1   ");
		
		if(null==paramMap){
			paramMap = new HashMap<String, Object>();
		}
		
		args = ArrayUtils.add(args, companyId);
		if(paramMap.containsKey("qcname")){
			sqlWhere.append(" and qcname like ?  ");
			args = ArrayUtils.add(args, paramMap.get("qcname") + "%");
		}
		
		if(paramMap.containsKey("pbcId")){
			sqlWhere.append(" and pbc_id = ?  ");
			args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("pbcId").toString(),-1));
		}
		
		
		if(paramMap.containsKey("tsno")){
			sqlWhere.append(" and (hava_no+g_hava_no) >= ?  ");
			args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("tsno").toString(),0));
		}
		
		if(paramMap.containsKey("teno")){
			sqlWhere.append(" and (hava_no+g_hava_no) <= ?  ");
			args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("teno").toString(),0));
		}
		
		if(paramMap.containsKey("ysno")){
			sqlWhere.append(" and (scan_no+g_scan_no) >= ?  ");
			args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("ysno").toString(),0));
		}
		
		if(paramMap.containsKey("yeno")){
			sqlWhere.append(" and (scan_no+g_scan_no)  <= ?  ");
			args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("yeno").toString(),0));
		}
		
		if(paramMap.containsKey("xsno")){
			sqlWhere.append(" and g_hava_no >= ?  ");
			args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("xsno").toString(),0));
		}
		
		if(paramMap.containsKey("xeno")){
			sqlWhere.append(" and g_hava_no <= ?  ");
			args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("xeno").toString(),0));
		}
		
		if(paramMap.containsKey("psno")){
			sqlWhere.append(" and hava_no >= ?  ");
			args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("psno").toString(),0));
		}
		
		if(paramMap.containsKey("peno")){
			sqlWhere.append(" and hava_no <= ?  ");
			args = ArrayUtils.add(args, NumberUtils.toInt(paramMap.get("peno").toString(),0));
		}
		
		if(paramMap.containsKey("is_print")){
			sqlWhere.append(" and is_print = ?  ");
			args = ArrayUtils.add(args, NumberUtils.toByte(paramMap.get("is_print").toString(),(byte) -1));
		}
		
		// 查询记录
		sqlQueryResult.append(sqlWhere).append("  ORDER BY c_time desc   ");
		List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
		return result;
		
	}

	public int insertQrcodeBase(String qcname, Integer pbcId, Integer nubs, UserSession userSession, Long nowlong,
			String admin) throws Exception{
		// TODO Auto-generated method stub
		String sql = "insert into qrcode_base (company_id,qcname,pbc_id,hava_no,g_hava_no,c_time,c_admin,is_history,source,type) values (?,?,?,?,?,?,?,?,?,?)";
	    KeyHolder keyHolder = new GeneratedKeyHolder();  
	    jdbcTemplate.update(new PreparedStatementCreator() { 
	    	@Override
	        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {  
	               PreparedStatement ps = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);  
	               ps.setInt(1, userSession.getCompanyId()); 
	               ps.setString(2,  qcname);
	               ps.setInt(3,  pbcId); 
	               ps.setInt(4, nubs*pbcId); 
	               ps.setInt(5, nubs); 
	               ps.setLong(6, nowlong); 
	               ps.setString(7, admin); 
	               ps.setInt(8, 1); 
	               ps.setInt(9, 1); 
	               ps.setInt(10, 3); 
	               return ps;  
	        }  
	    }, keyHolder);  
	    int generatedId = keyHolder.getKey().intValue();   
	    return generatedId; 
	}
	
	public int insertQrcodeDetailGroup(int qrcode_base_group_id, Integer nubs, Integer pbcId, Integer companyid) {
		// TODO Auto-generated method stub
		int tmpnubs = nubs;
		Long nowlong = Calendar.getInstance().getTimeInMillis();
		//System.out.println("start:"+nowlong);
		List<String> uuidlist = new ArrayList<String>(tmpnubs);
		 while(tmpnubs>maxexecutenub){
				String prefix1 = "insert into qrcode_detail_"+companyid+" (uuid,type,qrcode_base_id,source) values ";
				StringBuffer suffix1 = new StringBuffer(); 
				for(int i=0;i<maxexecutenub;i++){
					 String tmpuuid = UUID.randomUUID().toString().replace("-", "");
					 uuidlist.add(tmpuuid);
					 suffix1.append("( '" +tmpuuid+ "',"+1+","+qrcode_base_group_id+ ",1 ),");  
				}
			    String sql1 = prefix1 + suffix1.substring(0, suffix1.length() - 1);  
			    jdbcTemplate.execute(sql1);
			    //System.out.println("超过限制数量，执行限制数量添加--------------"+tmpnubs);
			    tmpnubs = tmpnubs - maxexecutenub;
		 }
		 if(tmpnubs>0){
				String prefix1 = "insert into qrcode_detail_"+companyid+" (uuid,type,qrcode_base_id,source) values ";
				StringBuffer suffix1 = new StringBuffer(); 
				for(int i=0;i<tmpnubs;i++){
					 String tmpuuid = UUID.randomUUID().toString().replace("-", "");
					 uuidlist.add(tmpuuid);
					 suffix1.append("( '" +tmpuuid+ "',"+1+","+qrcode_base_group_id+ ",1 ),");  
				}
			    String sql1 = prefix1 + suffix1.substring(0, suffix1.length() - 1);  
			    jdbcTemplate.execute(sql1);
			   // System.out.println("未超过限制数量，执行限制数量添加--------------"+tmpnubs);
		 }
		 int size = uuidlist.size();
		 int maxn = maxexecutenub/pbcId;
		 int count = 0;
		 while(size-count*maxn >= maxn){
			 String prefix2 = "insert into qrcode_detail_"+companyid+" (uuid,p_uuid,type,qrcode_base_id,source) values ";
			 StringBuffer suffix2 = new StringBuffer(); 
			 for(int i=0+count*maxn;i<(1+count)*maxn;i++){
				 for(int j=0;j<pbcId;j++){
					suffix2.append("( '" +UUID.randomUUID().toString().replace("-", "") +"','"+uuidlist.get(i)+"',"+0+","+qrcode_base_group_id+ ",1 ),");  
				 }
			 }
			 String sql2 = prefix2 + suffix2.substring(0, suffix2.length() - 1); 
			 jdbcTemplate.execute(sql2);
			 count++;
			// System.out.println("循环生成子二维码size="+size+";count="+count+";count*maxn="+count*maxn);
		 }
		 if(size-count*maxn > 0){
			 String prefix2 = "insert into qrcode_detail_"+companyid+" (uuid,p_uuid,type,qrcode_base_id,source) values ";
			 StringBuffer suffix2 = new StringBuffer(); 
			 for(int i=0+count*maxn;i<size;i++){
				 for(int j=0;j<pbcId;j++){
					suffix2.append("( '" +UUID.randomUUID().toString().replace("-", "") +"','"+uuidlist.get(i)+"',"+0+","+qrcode_base_group_id+ ",1 ),");  
				 }
			 }
			 String sql2 = prefix2 + suffix2.substring(0, suffix2.length() - 1); 
			 jdbcTemplate.execute(sql2);
			// System.out.println("最后生成子二维码size="+size+";count="+count+";count*maxn="+count*maxn);
		 }
	    Long afterlong = Calendar.getInstance().getTimeInMillis();
		//System.out.println("end:"+afterlong);
		//System.out.println("cost:"+(afterlong-nowlong));
		return 1;
	}

	public Map<String, Object> selectMinIDAndMaxIDByQrcodeBaseGroupID(int qrcode_base_group_id, Integer companyid) {
		// TODO Auto-generated method stub
		String sql = "SELECT MIN(CASE WHEN type = 0 THEN id END) minid,MAX(CASE WHEN type = 0 THEN id END) maxid , " +
					"	MIN(CASE WHEN type = 1 THEN id END) gminid,MAX(CASE WHEN type = 1 THEN id END) gmaxid "+
					"	 FROM qrcode_detail_"+companyid+" WHERE qrcode_base_id =? and source=1 ";
		
		Map<String, Object> rmap = jdbcTemplate.queryForMap(sql, qrcode_base_group_id);
		
		
		return rmap;
	}

	public void updateQrcodeGroupBH(int qrcode_base_group_id, Long start_no, Long end_no, Long g_start_no,
			Long g_end_no) {
		// TODO Auto-generated method stub
		
		String sql = "UPDATE qrcode_base SET start_no = ?,end_no = ?,g_start_no = ?,g_end_no = ? WHERE id = ? ";
		
		jdbcTemplate.update(sql, start_no,end_no,g_start_no,g_end_no,qrcode_base_group_id);
		
	}



//SELECT * FROM pro_product_qrcode ppq,product p,qrcode_base qb,qrcode_base_group qbg WHERE ppq.product_id = p.id AND ppq.qrcode_base_id = qb.id AND ppq.qrcode_base_group_id = qbg.id AND p.company_id = 1;


	public Pageable<Map<String, Object>> queryAllQrcodeProdcutCompnayID(Integer companyId,  Map<String, Object> paramMap, Integer page) {
		// TODO Auto-generated method stub
		Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("SELECT count(*) FROM ( " 
+ " SELECT ppq.id,ppq.batch_nub,ppq.is_act,p.`name` AS pname,p.id pid, qb.qcname AS qcname,p.number AS pnumber, ppq.qctype type, ppq.qrcode_s s_no,ppq.qrcode_e e_no,ppq.group_qrcode_s g_s_no, ppq.group_qrcode_e g_e_no, "
+ " ppq.qrcode_count q_count,ppq.group_qrcode_count  g_q_count,ppq.c_time,ppq.c_admin,ppq.u_time,ppq.u_admin ,c.pcount,ppq.sta, case when ISNULL(ppq.produce_date) = 0 then ppq.produce_date else 0 end  produce_date  "
+ " FROM pro_product_qrcode ppq,product p,pbc c,qrcode_base qb "
+ " WHERE ppq.product_id = p.id AND ppq.qrcode_base_id = qb.id  and p.pbc_id = c.id AND  ppq.company_id = ?  "
//+ " UNION ALL  "
//+ " SELECT ppq.id,ppq.batch_nub,ppq.is_act,p.`name` AS pname,p.number AS pnumber,p.id pid, qbg.qcname AS qcname, ppq.qctype type, ppq.qrcode_s s_no,ppq.qrcode_e e_no,ppq.group_qrcode_s g_s_no, ppq.group_qrcode_e g_e_no, "
//+ " ppq.qrcode_count q_count,ppq.group_qrcode_count  g_q_count,ppq.c_time,ppq.c_admin,ppq.u_time,ppq.u_admin  ,ppq.sta  "
//+ " FROM pro_product_qrcode ppq,product p,qrcode_base_group qbg  "
//+ " WHERE ppq.product_id = p.id AND ppq.qrcode_base_group_id = qbg.id   AND ppq.company_id = ?  "
+ " ) tmp ");
        StringBuffer sqlQueryResult = new StringBuffer("SELECT * FROM ( " 
        		+ " SELECT ppq.id,ppq.batch_nub,ppq.is_act,p.`name` AS pname,p.number AS pnumber,p.id pid, qb.qcname AS qcname, ppq.qctype type, ppq.qrcode_s s_no,ppq.qrcode_e e_no,ppq.group_qrcode_s g_s_no, ppq.group_qrcode_e g_e_no, "
        		+ " ppq.qrcode_count q_count,ppq.group_qrcode_count  g_q_count,ppq.c_time,ppq.c_admin,ppq.u_time,ppq.u_admin ,c.pcount,ppq.sta,case when ISNULL(ppq.produce_date) = 0 then ppq.produce_date else 0 end  produce_date  "
        		+ " FROM pro_product_qrcode ppq,product p,pbc c,qrcode_base qb "
        		+ " WHERE ppq.product_id = p.id AND ppq.qrcode_base_id = qb.id and p.pbc_id = c.id AND  ppq.company_id = ?  "
//        		+ " UNION ALL  "
//        		+ " SELECT ppq.id,ppq.batch_nub,ppq.is_act,p.`name` AS pname,p.number AS pnumber,p.id pid, qbg.qcname AS qcname, ppq.qctype type, ppq.qrcode_s s_no,ppq.qrcode_e e_no,ppq.group_qrcode_s g_s_no, ppq.group_qrcode_e g_e_no, "
//        		+ " ppq.qrcode_count q_count,ppq.group_qrcode_count  g_q_count,ppq.c_time,ppq.c_admin,ppq.u_time,ppq.u_admin  ,ppq.sta  "
//        		+ " FROM pro_product_qrcode ppq,product p,qrcode_base_group qbg  "
//        		+ " WHERE ppq.product_id = p.id AND ppq.qrcode_base_group_id = qbg.id   AND ppq.company_id = ?  "
        		+ " ) tmp ");

        StringBuffer sqlWhere = new StringBuffer(" WHERE 1=1 ");
       
        args = ArrayUtils.add(args, companyId);
       // args = ArrayUtils.add(args, companyId);
        
        if(null==paramMap){
        	paramMap = new HashMap<String, Object>();
		}
		
		if(paramMap.containsKey("pname")){
        	sqlWhere.append(" and (tmp.pname like ?  or tmp.pnumber like ?)");
        	args = ArrayUtils.add(args, paramMap.get("pname") + "%");
        	args = ArrayUtils.add(args, "%"+paramMap.get("pname") + "%");
        }
		
		if(paramMap.containsKey("batch_nub")){
        	sqlWhere.append(" and tmp.batch_nub like ? ");
        	args = ArrayUtils.add(args, "%"+paramMap.get("batch_nub") + "%");
        }
     
		if(paramMap.containsKey("qcname")){
        	sqlWhere.append(" and tmp.qcname like ?  ");
        	args = ArrayUtils.add(args, paramMap.get("qcname") + "%");
        }
		
		if(paramMap.containsKey("sta")){
        	sqlWhere.append(" and tmp.sta = ?  ");
        	args = ArrayUtils.add(args, NumberUtils.toByte(paramMap.get("sta").toString(),(byte) 0));
        }
		
		if(paramMap.containsKey("is_act")){
        	sqlWhere.append(" and tmp.is_act = ?  ");
        	args = ArrayUtils.add(args, NumberUtils.toByte(paramMap.get("is_act").toString(),(byte) 0));
        }
		
        
        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  ORDER BY tmp.sta desc,c_time desc   limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return new Pageable<>(page, getPageSize(), resultTotal, result);
	}
	/**
	 * 二维码绑定数据列表（用于数据导出）
	 * @param companyId 公司id
	 * @param paramMap 查询条件集合
	 * @return List<Map<String, Object>>
	 * @date 2016-12-07 12:01
	 * */
	public List<Map<String, Object>> queryAllQrcodeProdcutCompnayIDForExport(Integer companyId,  Map<String, Object> paramMap) {
		Object[] args = {};
		
		StringBuffer sqlQueryResult = new StringBuffer("SELECT * FROM ( " 
				+ " SELECT ppq.id,p.`name` AS pname,p.number AS pnumber,p.id pid, qb.qcname AS qcname, ppq.qctype type, ppq.qrcode_s s_no,ppq.qrcode_e e_no,ppq.group_qrcode_s g_s_no, ppq.group_qrcode_e g_e_no, "
				+ " ppq.qrcode_count q_count,ppq.group_qrcode_count  g_q_count,ppq.c_time,ppq.c_admin,ppq.u_time,ppq.u_admin ,ppq.sta,ppq.produce_date  "
				+ " FROM pro_product_qrcode ppq,product p,qrcode_base qb "
				+ " WHERE ppq.product_id = p.id AND ppq.qrcode_base_id = qb.id  AND  ppq.company_id = ?  "
//				+ " UNION ALL  "
//				+ " SELECT ppq.id,p.`name` AS pname,p.number AS pnumber,p.id pid, qbg.qcname AS qcname, ppq.qctype type, ppq.qrcode_s s_no,ppq.qrcode_e e_no,ppq.group_qrcode_s g_s_no, ppq.group_qrcode_e g_e_no, "
//				+ " ppq.qrcode_count q_count,ppq.group_qrcode_count  g_q_count,ppq.c_time,ppq.c_admin,ppq.u_time,ppq.u_admin  ,ppq.sta  "
//				+ " FROM pro_product_qrcode ppq,product p,qrcode_base_group qbg  "
//				+ " WHERE ppq.product_id = p.id AND ppq.qrcode_base_group_id = qbg.id   AND ppq.company_id = ?  "
				+ " ) tmp ");
		
		StringBuffer sqlWhere = new StringBuffer(" WHERE 1=1 ");
		
		args = ArrayUtils.add(args, companyId);
//		args = ArrayUtils.add(args, companyId);
		
		if(null==paramMap){
			paramMap = new HashMap<String, Object>();
		}
		
		if(paramMap.containsKey("pname")){
			sqlWhere.append(" and (tmp.pname like ?  or tmp.pnumber like ?)");
			args = ArrayUtils.add(args, paramMap.get("pname") + "%");
			args = ArrayUtils.add(args, "%"+paramMap.get("pname") + "%");
		}
		
		if(paramMap.containsKey("qcname")){
			sqlWhere.append(" and tmp.qcname like ?  ");
			args = ArrayUtils.add(args, paramMap.get("qcname") + "%");
		}
		
		if(paramMap.containsKey("sta")){
			sqlWhere.append(" and tmp.sta = ?  ");
			args = ArrayUtils.add(args, NumberUtils.toByte(paramMap.get("sta").toString(),(byte) 0));
		}
		
		// 查询记录
		sqlQueryResult.append(sqlWhere).append("  ORDER BY tmp.sta desc,c_time desc ");
		
		List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
		return result;
	}






	public List<Map<String, Object>> queryProductName(String pname, Integer limit,Integer company_id) {
		// TODO Auto-generated method stub
		String sql = "select t.id,t.name,t.number,c.pcount from product t,pbc c where t.pbc_id=c.id and t.state=1 and t.company_id=? and (t.name like ? or t.number like ?) limit ?";
		
		return jdbcTemplate.queryForList(sql,company_id,pname+"%","%"+pname+"%",limit);
	}






	public List<Map<String, Object>> queryQrcodeName(String qcname,Integer type, Integer limit, Integer companyId) {
		// TODO Auto-generated method stub
		String sql = "";
		if(1==type || 2==type){
			sql = "select id,qcname as name from qrcode_base where company_id=? and source=0 and type=? and (qcname like ? or id like ?) limit ?";
			return jdbcTemplate.queryForList(sql,companyId,type,qcname+"%",qcname+"%",limit);
		}else if(3==type){
			sql = "select id,qcname  as name from qrcode_base where company_id=? and source=1 and type=? and (qcname like ? or id like ? ) limit ?";
			return jdbcTemplate.queryForList(sql,companyId,type,qcname+"%",qcname+"%",limit);
		}else{
			return new ArrayList<Map<String, Object>>();
		}
	}




	public Map<String,Object>  queryQrcodeNoBangdingCount(Integer id,Integer type, Integer companyid) {
		// TODO Auto-generated method stub
		
		String sql = "";
		
		if(1==type || 2==type){
			sql = "select count(*) as count,min(id) as minid,max(id) as maxid from qrcode_detail_"+companyid+" where qrcode_base_id=?  and sta=1 and product_id is null and source=0 ";
			
			return jdbcTemplate.queryForMap(sql, id);
			
		}else if(3==type){
			sql = "select (select  pbc_id FROM qrcode_base WHERE id = ? and source=1 ) AS pbc_id ,count(case when type=1 then id end) as count,min(case when type=0 then id end) as minid ,max(case when type=0 then id end) as maxid ,min(case when type=1 then id end) as xminid ,max(case when type=1 then id end) as xmaxid from qrcode_detail_"+companyid+" where qrcode_base_id=?  and  sta=1 and  product_id is null and source=1 ";
			return jdbcTemplate.queryForMap(sql, id,id);
			
		}else{
			
			return new HashMap<String, Object>();
		}
	}

	public Map<String,Object>  queryQrcodePbcchangproduct(Integer id) {
		// TODO Auto-generated method stub
		String sql = "SELECT b.pbc_id pcount FROM qrcode_base b,pro_product_qrcode p WHERE p.qrcode_base_id = b.id AND p.id =? ";
//		String sql = "select c.pcount from qrcode_base b ,pbc c,pro_product_qrcode p  where b.pbc_id = c.id and p.qrcode_base_id = b.id and p.id=? ";
		return jdbcTemplate.queryForMap(sql, id);
		
	}




	public Integer insertQrcodeProduct(Integer pid, Long qcid, Integer type, Long pms, Long pme,
			UserSession userSession,String batch_nub,long produce_date) {
		// TODO Auto-generated method stub
		String sql = "insert into pro_product_qrcode (product_id,qrcode_base_id,qctype,qrcode_s,qrcode_e,qrcode_count,c_time,c_admin,sta,company_id,batch_nub,produce_date) values (?,?,?,?,?,?,?,?,?,?,?,?)";
	    KeyHolder keyHolder = new GeneratedKeyHolder();  
	    jdbcTemplate.update(new PreparedStatementCreator() { 
	    	@Override
	        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {  
	    		   PreparedStatement ps = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);  
	               ps.setInt(1, pid); 
	               ps.setLong(2,  qcid);
	               ps.setInt(3,  type); 
	               ps.setLong(4, pms); 
	               ps.setLong(5, pme); 
	               ps.setLong(6, pme-pms+1); 
	               ps.setLong(7, Calendar.getInstance().getTimeInMillis()); 
	               ps.setString(8, userSession.getId_admin()); 
	               ps.setByte(9, (byte) 1); 
	               ps.setInt(10, userSession.getCompanyId()); 
	               ps.setString(11, batch_nub);
	               ps.setLong(12, produce_date);
	              
	               return ps;  
	        }  
	    }, keyHolder);  
	    int generatedId = keyHolder.getKey().intValue();   
	    return generatedId; 
	}

	public Integer insertQrcodeGroupProduct(Integer pid, Long qcid, Integer type, Long pms, Long pme, Long xms,
			Long xme, UserSession userSession,String batch_nub,long produce_date) {
		String sql = "insert into pro_product_qrcode (product_id,qrcode_base_id,qctype,qrcode_s,qrcode_e,qrcode_count,"
				+ "group_qrcode_s,group_qrcode_e,group_qrcode_count,c_time,c_admin,sta,company_id,batch_nub,produce_date) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	    KeyHolder keyHolder = new GeneratedKeyHolder();  
	    jdbcTemplate.update(new PreparedStatementCreator() { 
	    	@Override
	        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {  
	               PreparedStatement ps = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);  
	               ps.setInt(1, pid); 
	               ps.setLong(2,  qcid);
	               ps.setInt(3,  type); 
	               ps.setLong(4, pms); 
	               ps.setLong(5, pme); 
	               ps.setLong(6, pme-pms+1); 
	               ps.setLong(7, xms); 
	               ps.setLong(8, xme); 
	               ps.setLong(9, xme-xms+1);
	               ps.setLong(10, Calendar.getInstance().getTimeInMillis()); 
	               ps.setString(11, userSession.getId_admin()); 
	               ps.setByte(12, (byte) 1); 
	               ps.setInt(13, userSession.getCompanyId()); 
	               ps.setString(14, batch_nub);
	               ps.setLong(15, produce_date);
	               return ps;  
	        }  
	    }, keyHolder);  
	      
	    int generatedId = keyHolder.getKey().intValue();   
	    return generatedId; 
	
	}






	public Integer updateQrcodeDetailOfProduct(Integer pid, Long qcid,Integer ppqid, Long pms, Long pme,Integer companyid) {
		// TODO Auto-generated method stub
		String sql = "update qrcode_detail_"+companyid+" set product_id = ?,pro_product_qrcode_id = ? where qrcode_base_id = ? and id>=? and id<=?";
		return jdbcTemplate.update(sql,pid,ppqid,qcid,pms,pme);
	}






	public Integer updateQrcodeDetailGroupOfProduct(Integer pid, Long qcid,Integer ppqid, Long pms, Long pme, Long xms, Long xme,Integer companyid) {
		// TODO Auto-generated method stub
		String sql = "update qrcode_detail_"+companyid+" set product_id = ?,pro_product_qrcode_id = ? where qrcode_base_id = ? and (( id>=? and id<=? and type=0 ) or ( id>=? and id<=? and type=1 ))";
		return jdbcTemplate.update(sql,pid,ppqid,qcid,pms,pme,xms,xme);
	}






	public Long queryQrcodeProductResetEndNoByXme(Integer id,Integer companyid) {
		// TODO Auto-generated method stub
		String sql = "SELECT MAX(id) AS cmaxid FROM qrcode_detail_"+companyid+" WHERE p_uuid = (select  uuid FROM qrcode_detail_"+companyid+" WHERE id = ?);";
		return jdbcTemplate.queryForObject(sql, Long.class,id);
	}






	public void deleteQrcodeProductById(Integer id,UserSession userSession) {
		// TODO Auto-generated method stub
		String sql = "update pro_product_qrcode set sta=0,u_time=?,u_admin=? where id=? and company_id = ?  ";
		jdbcTemplate.update(sql,Calendar.getInstance().getTimeInMillis(),userSession.getId_admin(), id,userSession.getCompanyId());
	}






	public void updateQrcodeDetaiForNoBangding(Integer id, UserSession userSession) {
		// TODO Auto-generated method stub
		String sql = "update qrcode_detail_"+userSession.getCompanyId()+" set sta=2 where pro_product_qrcode_id=? and pro_product_qrcode_id = (select id from pro_product_qrcode where id=? and company_id = ?  )";
		jdbcTemplate.update(sql, id,id,userSession.getCompanyId());
	}






	public void updateQrcodeDetaiGroupForNoBangding(Integer id, UserSession userSession) {
		// TODO Auto-generated method stub
		String sql = "update qrcode_detail_"+userSession.getCompanyId()+" set sta=2 where pro_product_qrcode_id=?  and pro_product_qrcode_id = (select id from pro_product_qrcode where id=? and company_id = ?  )";
		jdbcTemplate.update(sql,id,id,userSession.getCompanyId());
	}






	public Pageable<Map<String, Object>> queryQrcodeDetailByID(Integer companyId, String id,String proName, String qrId, Integer state,
			Integer page) {
		if(null==id ||id.equals("")){
			return new Pageable<>(page, getPageSize(), 0, new ArrayList<Map<String, Object>>());
		}
	
		Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select COUNT(*) from (SELECT qd.id,qd.uuid,qd.qrcode_base_id,qd.product_id," +
				"qd.pro_product_qrcode_id,qd.scan_times,qd.first_scan_time,qd.sta ,p.`name` AS pname  " +
				"FROM qrcode_detail_"+companyId+" qd LEFT JOIN  product p ON qd.product_id = p.id WHERE qrcode_base_id = ?"
        		+ " and qrcode_base_id = (SELECT id FROM qrcode_base WHERE id= ? AND company_id = ?) ");
//        		+ " ) aa join log_res_qrcode l on aa.id=l.p_id  and  (SELECT company_id FROM qrcode_base WHERE id= ? AND company_id = ?) = l.companyid ");

        StringBuffer sqlQueryResult = new StringBuffer("select aa.*,l.uuid w_uuid from (SELECT qd.id,qd.uuid,qd.qrcode_base_id,qd.product_id," +
				"qd.pro_product_qrcode_id,qd.scan_times,qd.first_scan_time,qd.sta ,p.`name` AS pname " +
				" FROM qrcode_detail_"+companyId+" qd LEFT JOIN  product p ON qd.product_id = p.id WHERE qrcode_base_id = ?"
        		+ " and qrcode_base_id = (SELECT id FROM qrcode_base WHERE id= ? AND company_id = ?) ");
		StringBuffer sqlQueryResult1 =  new StringBuffer(" )aa join log_res_qrcode l on aa.id=l.p_id  and  (SELECT company_id FROM qrcode_base WHERE id= ? AND company_id = ?) = l.companyid ");

      
        args = ArrayUtils.add(args, id);
        args = ArrayUtils.add(args, id);
        args = ArrayUtils.add(args, companyId);

		StringBuffer sqlWhere = new StringBuffer(" ");
		if(StringUtils.isNotBlank(proName)){
			sqlWhere.append(" and p.name like " + "'%" + proName.trim() + "%'");
		}
		if(StringUtils.isNotBlank(qrId)){
			sqlWhere.append(" and qd.id = " + qrId.trim());
		}
		if(state != null){
			sqlWhere.append(" and qd.sta = " + state);
		}

		args = ArrayUtils.add(args, id);
		args = ArrayUtils.add(args, companyId);
        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).append(sqlQueryResult1).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append(sqlQueryResult1).append("  limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        if(result!= null && result.size()>0){
        	return new Pageable<>(page, getPageSize(), resultTotal, result);
        }else{
        	Object[] args1 = {};
        	args1 = ArrayUtils.add(args1, id);
            args1 = ArrayUtils.add(args1, id);
            args1 = ArrayUtils.add(args1, companyId);
            
        	sqlQueryResultCount = new StringBuffer("SELECT count(1) FROM qrcode_detail_"+companyId+" qd LEFT JOIN  product p ON qd.product_id = p.id WHERE qrcode_base_id = ? and qrcode_base_id = (SELECT id FROM qrcode_base WHERE id= ? AND company_id = ?) ");
        	resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args1);
        	sqlQueryResult = new StringBuffer("SELECT qd.id,qd.uuid,qd.qrcode_base_id,qd.product_id,qd.pro_product_qrcode_id,qd.scan_times,qd.first_scan_time,qd.sta ,p.`name` AS pname  FROM qrcode_detail_"+companyId+" qd LEFT JOIN  product p ON qd.product_id = p.id WHERE qrcode_base_id = ? and qrcode_base_id = (SELECT id FROM qrcode_base WHERE id= ? AND company_id = ?) ");

        	sqlQueryResult.append(sqlWhere).append("  limit ?,?");
        	args1 = ArrayUtils.add(args1, (page - 1) * getPageSize());
            args1 = ArrayUtils.add(args1, getPageSize());
            
        	result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args1);
        	return new Pageable<>(page, getPageSize(), resultTotal, result);
        }
        
	}

	public Pageable<Map<String, Object>> queryQrcodeBindingByID(Integer companyId, Map<String, Object> paramMap,
			Integer page) {
		// TODO Auto-generated method stub
		if(null==paramMap || !paramMap.containsKey("id")){
			return new Pageable<>(page, getPageSize(), 0, new ArrayList<Map<String, Object>>());
		}
	
		Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer(" SELECT COUNT(*)  FROM pro_product_qrcode ppq,product p,qrcode_base qb  "
        		 +" WHERE ppq.product_id = p.id AND ppq.qrcode_base_id = qb.id  AND  p.company_id =?  AND qb.id = ?");
        StringBuffer sqlQueryResult = new StringBuffer("SELECT ppq.id,p.`name` AS pname, qb.qcname AS qcname, ppq.qctype type, ppq.qrcode_s s_no,ppq.qrcode_e e_no, " 
        		+" ppq.qrcode_count q_count,ppq.c_time,ppq.c_admin,ppq.u_time,ppq.u_admin ,ppq.sta   "
        		+"  FROM pro_product_qrcode ppq,product p,qrcode_base qb  "
        		 +" WHERE ppq.product_id = p.id AND ppq.qrcode_base_id = qb.id  AND  p.company_id = ?  AND qb.id = ?");

        
        args = ArrayUtils.add(args, companyId);
        
        Integer id = NumberUtils.toInt(paramMap.get("id").toString(), -1);
      
        args = ArrayUtils.add(args, id);
        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append("  limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return new Pageable<>(page, getPageSize(), resultTotal, result);

	}




	public Pageable<Map<String, Object>> queryQrcodegroupBindingByID(Integer companyId, Map<String, Object> paramMap,
			Integer page) {
		// TODO Auto-generated method stub
		// TODO Auto-generated method stub
		if(null==paramMap || !paramMap.containsKey("id")){
			return new Pageable<>(page, getPageSize(), 0, new ArrayList<Map<String, Object>>());
		}
	
		Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer(" SELECT COUNT(*)  FROM pro_product_qrcode ppq,product p,qrcode_base qbg  "
        	 +" WHERE ppq.product_id = p.id AND ppq.qrcode_base_id = qbg.id   AND p.company_id = ?  AND qbg.id = ?");
        StringBuffer sqlQueryResult = new StringBuffer(" SELECT ppq.id,p.`name` AS pname, qbg.qcname AS qcname, ppq.qctype type, ppq.qrcode_s s_no,ppq.qrcode_e e_no,ppq.group_qrcode_s g_s_no, ppq.group_qrcode_e g_e_no, "
        	 +" ppq.qrcode_count q_count,ppq.group_qrcode_count  g_q_count,ppq.c_time,ppq.c_admin,ppq.u_time,ppq.u_admin  ,ppq.sta  "
        	 +" FROM pro_product_qrcode ppq,product p,qrcode_base qbg  "
        	 +" WHERE ppq.product_id = p.id AND ppq.qrcode_base_id = qbg.id   AND p.company_id = ?  AND qbg.id = ?");

        
        args = ArrayUtils.add(args, companyId);
        
        Integer id = NumberUtils.toInt(paramMap.get("id").toString(), -1);
      
        args = ArrayUtils.add(args, id);
        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append("  limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return new Pageable<>(page, getPageSize(), resultTotal, result);

	}






	public Pageable<Map<String, Object>> queryQrcodegroupDetailByID(Integer companyId, String  id,String proName, String qrId, Integer state,
			Integer page) {
		// TODO Auto-generated method stub
		if(null==id || id.equals("")){
			return new Pageable<>(page, getPageSize(), 0, new ArrayList<Map<String, Object>>());
		}
	
		Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select COUNT(*) from (SELECT qd.id,qd.uuid,qd.p_uuid,qd.qrcode_base_id,qd.type,qd.product_id,qd.pro_product_qrcode_id,qd.scan_times,qd.first_scan_time,qd.sta ,p.`name` AS pname  "
        		+ "FROM qrcode_detail_"+companyId+" qd LEFT JOIN  product p ON qd.product_id = p.id WHERE qrcode_base_id = ? "
        				+ "and qrcode_base_id = (SELECT id FROM qrcode_base WHERE id= ? AND company_id = ?) ");
 
        StringBuffer sqlQueryResult = new StringBuffer("select aa.*,l.uuid w_uuid from (SELECT qd.id,qd.uuid,qd.p_uuid,qd.qrcode_base_id,qd.type,qd.product_id,qd.pro_product_qrcode_id,qd.scan_times,qd.first_scan_time,qd.sta ,p.`name` AS pname  "
        		+ "FROM qrcode_detail_"+companyId+" qd LEFT JOIN  product p ON qd.product_id = p.id WHERE qrcode_base_id = ? "
        				+ "and qrcode_base_id = (SELECT id FROM qrcode_base WHERE id= ? AND company_id = ?)  ");

		StringBuffer sqlQueryResult1 = new StringBuffer(" ) aa join log_res_qrcode l on aa.id=l.p_id and  " +
				"(SELECT company_id FROM qrcode_base WHERE id= ? AND company_id = ?) = l.companyid ");
        args = ArrayUtils.add(args, id);
        args = ArrayUtils.add(args, id);
        args = ArrayUtils.add(args, companyId);


		StringBuffer sqlWhere = new StringBuffer(" ");
		if(StringUtils.isNotBlank(proName)){
			sqlWhere.append(" and p.name like ?");
			args = ArrayUtils.add(args, "%" + proName.trim() + "%");
		}
		if(StringUtils.isNotBlank(qrId)){
			sqlWhere.append(" and qd.id = ?");
			args = ArrayUtils.add(args, qrId.trim());
		}
		if(state != null){
			sqlWhere.append(" and qd.sta = ?");
			args = ArrayUtils.add(args, state);
		}
		args = ArrayUtils.add(args, id);
		args = ArrayUtils.add(args, companyId);
        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).append(sqlQueryResult1).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append(sqlQueryResult1).append("  limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return new Pageable<>(page, getPageSize(), resultTotal, result);

	}






	public void updateQrcodeProduct(Integer id, Integer pid,String batch_nub,String produce_date, UserSession userSession) {
		// TODO Auto-generated method stub
		String sql = "update pro_product_qrcode set product_id=?,batch_nub = ?,produce_date=?,u_time=?,u_admin=? where id=? and company_id = ? ";
		jdbcTemplate.update(sql, pid,batch_nub,produce_date,Calendar.getInstance().getTimeInMillis(),userSession.getId_admin(),id,userSession.getCompanyId());
	}






	public void updateQrcodeDetailOfProductForPid(Integer id, Integer pid, UserSession userSession) {
		// TODO Auto-generated method stub
		String sql = "update qrcode_detail_"+userSession.getCompanyId()+" set product_id=? where pro_product_qrcode_id=?  and pro_product_qrcode_id = (select id from pro_product_qrcode where id=? and company_id = ?  )";
		jdbcTemplate.update(sql, pid,id,id,userSession.getCompanyId());
	}
	
	public void updateQrcodeDetailGroupOfProductForPid(Integer id, Integer pid, UserSession userSession) {
		// TODO Auto-generated method stub
		String sql = "update qrcode_detail_"+userSession.getCompanyId()+" set product_id=? where pro_product_qrcode_id=?  and pro_product_qrcode_id = (select id from pro_product_qrcode where id=? and company_id = ?  )";
		jdbcTemplate.update(sql, pid,id,id,userSession.getCompanyId());
	}






	/**
	 * @author kelan  修改逻辑
	 * @date 2017年3月8日
	 * @param id
	 * @param isAutoDraw isAutoDraw =2 历史生成的二维码，0或者1 是改造后的二维码走新的数据结构取物流码
	 * @param userSession
	 * @return
	 */
	public List<Map<String, Object>> queryQrcodeInfoForDownload(Integer id,Integer isAutoDraw,UserSession userSession) {
		// TODO Auto-generated method stub
		String sql = "";
		if(isAutoDraw==2){
			 sql = "SELECT qb.type,qd.id,qd.uuid from qrcode_base qb ,qrcode_detail_"+userSession.getCompanyId()+" qd WHERE qb.id = qd.qrcode_base_id AND qb.id = ? AND qb.company_id=? ";
		}else{
			 sql = "SELECT qb.type,qd.id,qd.uuid,res.pcode,res.uuid w_uuid from qrcode_base qb ,qrcode_detail_"+userSession.getCompanyId()+" qd,log_res_qrcode res WHERE qb.id = qd.qrcode_base_id and qd.id=res.p_id and qb.company_id = res.companyid AND qb.id = ? AND qb.company_id=?  and qb.source=0 ";
		}
		return jdbcTemplate.queryForList(sql,id,userSession.getCompanyId());
	}



	public List<Map<String, Object>> queryQrcodeGroupInfoForDownload(Integer id,UserSession userSession) {
		// TODO Auto-generated method stub
		String sql="SELECT q.id,q.uuid,q.p_uuid,l.pcode,l.uuid w_uuid,q.type FROM qrcode_detail_"+userSession.getCompanyId()+" q LEFT JOIN log_res_qrcode l ON q.id = l.p_id left join qrcode_base b on q.qrcode_base_id=b.id WHERE q.qrcode_base_id = ? and q.source=1 and b.company_id = ? and b.company_id = l.companyid ";
		return jdbcTemplate.queryForList(sql,id,userSession.getCompanyId());
	}


	public void updateQrcodeBaseOfBandings(Long qcid, Long pms, Long pme) {
		// TODO Auto-generated method stub
		String sql = "UPDATE qrcode_base SET  bandings_nub = bandings_nub+? WHERE id = ?";
		jdbcTemplate.update(sql,pme-pms+1,qcid);
	}


	public void updateQrcodeBaseGroupOfBandings(Long qcid, Long pms, Long pme, Long xms, Long xme) {
		// TODO Auto-generated method stub
		String sql = "UPDATE qrcode_base SET  bandings_nub = bandings_nub+?,g_bandings_nub = g_bandings_nub+? WHERE id = ?";
		jdbcTemplate.update(sql,pme-pms+1,xme-xms+1,qcid);
	}




	public void updateDownlaodState(Integer id) {
		// TODO Auto-generated method stub
		String sql = "UPDATE qrcode_base SET  is_print = ? WHERE id = ?";
		jdbcTemplate.update(sql,1,id);
	}






	public void updateGroupDownlaodState(Integer id) {
		String sql = "UPDATE qrcode_base SET  is_print = ? WHERE id = ?";
		jdbcTemplate.update(sql,1,id);
	}

	public Integer selectIsExistBandingOfQrcode(Long qcid, Long pms, Long pme) {
		String sql = "SELECT COUNT(*) AS n FROM pro_product_qrcode WHERE qrcode_base_id = ? AND  qrcode_e >= ?";
		
		return jdbcTemplate.queryForObject(sql, Integer.class,qcid,pms);
	}

	public Integer selectIsExistBandingOfQrcodeGroup(Long qcid, Long pms, Long pme) {
		String sql = "SELECT COUNT(*) AS n FROM pro_product_qrcode WHERE qrcode_base_id = ? AND  group_qrcode_e >= ?";
		
		return jdbcTemplate.queryForObject(sql, Integer.class,qcid,pms);
	}

	public Integer updateQrcodeProductActSta(Integer id, Integer type, UserSession userSession) {
		String sql = "UPDATE pro_product_qrcode SET is_act = ?,u_time=?,u_admin=? WHERE id = ?";
		return jdbcTemplate.update(sql,type,Calendar.getInstance().getTimeInMillis(),userSession.getId_admin(),id);
	}
}
